/* ******************************************************************************************************* */
/* *********** Constructs a dataset containing fund votes on governance proposals and holdings  ************/
/* ******************************************************************************************************* */

data Vavoteresults; set iss.Vote_Results2003_2018;
  if index(ItemDesc, 'Elect Director') = 1 then delete;
  if index(ItemDesc, 'Elect  Directors') = 1 then delete;
  if VoteRequirement = 66.67 then VoteRequirement = .66;
  if VoteRequirement = .01 and ISSAgendaItemID not in ('M0201', 'M0299', 'S0299') then VoteRequirement = .5; *fix data errors;
  if votedfor+votedagainst <= 1 then delete; *likely data errors;

  if MGMTrec = 'Against' then do;
    if voteresult = 'Pass' then do; mgmt_win = 0; margin_mgmt = -abs(margin); end; else if voteresult = 'Fail' then do; mgmt_win = 1; margin_mgmt = abs(margin); end; end;
  if MGMTrec = 'For' then do;
    if voteresult = 'Pass' then do; mgmt_win = 1; margin_mgmt = abs(margin); end; else if voteresult = 'Fail' then do; mgmt_win = 0; margin_mgmt = -abs(margin); end; end;

  if base in ('F+A', 'F A') then margin = votedfor/(votedfor+votedagainst)-voterequirement;
  if base in ('F+A+AB', 'F A AB') then margin = votedfor/(votedfor+votedagainst+votedabstain)-voterequirement;
  if base = 'Outstanding' and outstandingshare > 0 then margin = votedfor/outstandingshare-voterequirement;

proc sql;
  create table iss_votes as
  select a.*, b.mgmtrec as mgtrecvote, b.issrec as issrecvote, b.margin, b.voteResult, b.base
  from temp.ISS_votes as a, Vavoteresults as b
  where a.companyId = b.companyId and a.cusip = b.cusip and a.meetingID = b.meetingID and a.issAgendaItemId = b.issAgendaItemId and a.itemonagendaid = b.itemonagendaid;
quit;
 
data iss_votes; set iss_votes;
  if fundvote not in ('<Unknown>', 'Do Not Vote', 'None') then voteWithMgmt = 0;
  if mgtrecvote = "Abstain" and fundvote = "Abstain" then voteWithMgmt=1;
  if mgtrecvote = "Withhold" and fundvote = "Withhold" then voteWithMgmt=1;
  if mgtrecvote = "Against" and fundvote = "Against" then voteWithMgmt=1; 
  if mgtrecvote = "For" and fundvote = "For" then voteWithMgmt=1;
  if mgtrecvote = "One Year" and fundvote = "One Year" then voteWithMgmt=1;
  if mgtrecvote = "Two Years" and fundvote = "Two Years" then voteWithMgmt=1;
  if mgtrecvote = "Three Years" and fundvote = "Three Years" then voteWithMgmt=1;
  if mgtrecvote = "Do Not Vote" and fundvote = "Do Not Vote" then voteWithMgmt=1;

  if mgtrecvote ne '' then mgmtFor=0;
  if mgtrecvote = "For" then mgmtFor=1;

  if fundvote ne '' then fundFor=0;
  if fundvote = "For" then fundFor=1;

  if issrecvote ne '' then issFor=0;
  if issrecvote = "For" then issFor=1;
  
  if fundvote ne '' then fundAgainst=0;
  if fundvote = "Against" then fundAgainst=1;

  if voteResult = 'Pass' and margin > 0 then do;
    if fundvote = "For" then WinVote = 1; else 
    if fundvote = "Against" then WinVote = 0; else 
    if fundvote in ("Abstain", "Withhold") and base in ('F+A+AB', 'F A AB') then WinVote = 0; 
  end;

  if voteResult = 'Fail' and margin < 0 then do;
    if fundvote = "Against" then WinVote = 1; else 
    if fundvote = "For" then WinVote = 0; else 
	if fundvote in ("Abstain", "Withhold") and base in ('F+A+AB', 'F A AB') then WinVote = 1; 
end;

  if substr(issagendaitemid,1,1) = "S" then mgmt_sponsored = 0;
  if substr(issagendaitemid,1,1) = "M" then mgmt_sponsored = 1;

  *** issForMgmt is defined as an indicator that equals one if ISS recommend voting with management;
  if mgtrecvote = issrecvote and mgtrecvote in ("For", "Against") then issForMgmt=1; else 
  if mgtrecvote ne issrecvote and mgtrecvote in ("For", "Against") and issrecvote in ("For", "Against") then issForMgmt=0; else
  if (mgtrecvote = "Abstain" or index(mgtrecvote, "Do Not V") = 1) and issrecvote in ("Abstain", "Do Not Vote", "Withhold", "None") then issForMgmt=1; else
  if mgmt_sponsored = 1 and issrecvote in ("For") then issForMgmt=1; else
  if mgmt_sponsored = 1 and issrecvote in ("Do Not Vote") then issForMgmt=0; else
  if mgtrecvote = "Withhold" and issrecvote in ("Abstain", "Do Not Vote", "Withhold") then issForMgmt=1; else
  if substr(upcase(mgtrecvote), 1, 3) = substr(upcase(issrecvote), 1, 3) and index(upcase(mgtrecvote), "YE") > 0 and index(upcase(issrecvote), "YE") > 0 then issForMgmt=1; else
  if substr(upcase(mgtrecvote), 1, 3) ne substr(upcase(issrecvote), 1, 3) and index(upcase(mgtrecvote), "YE") > 0 and index(upcase(issrecvote), "YE") > 0 then issForMgmt=0;


  if fundvote = issrecvote and fundvote in ("For", "Against") then iss_conform=1; else 
  if fundvote ne issrecvote and fundvote in ("For", "Against") and issrecvote in ("For", "Against") then iss_conform=0; else
  if (fundvote = "Abstain" or index(fundvote, "Do Not V") = 1) and issrecvote in ("Abstain", "Do Not Vote", "Withhold", "None") then iss_conform=1; else
  if fundvote = "Withhold" and issrecvote in ("Abstain", "Do Not Vote", "Withhold") then iss_conform=1; else
  if substr(upcase(fundvote), 1, 3) = substr(upcase(issrecvote), 1, 3) and index(upcase(fundvote), "YE") > 0 and index(upcase(issrecvote), "YE") > 0 then iss_conform=1; else
  if substr(upcase(fundvote), 1, 3) ne substr(upcase(issrecvote), 1, 3) and index(upcase(fundvote), "YE") > 0 and index(upcase(issrecvote), "YE") > 0 then iss_conform=0;

  if WinVote not in (0, 1) then delete;

proc means n mean median p25 p75 data = iss_votes;
  var voteWithMgmt WinVote; 
run;


proc sort data = iss_votes nodupkey; by cusip meetingID meetingdate mgmt_sponsored issAgendaItemId itemonagendaid institutionid fundid fundvote;
run;

* add PERMNO - note obs with no match drop;;
proc sort data=crsp.stocknames(keep=permno ncusip cusip ticker namedt nameenddt shrcd exchcd where=(not missing(ncusip))) 
  out=cusips nodupkey; by permno ncusip;
run;

proc sql;
  create table iss_votes2 as
  select a.*, b.permno, min(intck('month', b.namedt, a.meetingdate), intck('month', a.meetingdate, b.nameenddt)) as dist
  from iss_votes as a left join cusips as b
  on substr(a.cusip, 1, 8) = b.ncusip and b.namedt <= a.meetingdate <= nameenddt;
quit;

proc sort; by cusip meetingdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid institutionid fundid mgmtFor issForMgmt issFor fundvote issue dist;

data iss_votes2; set iss_votes2; by cusip meetingdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid institutionid fundid mgmtFor issForMgmt issFor fundvote issue dist;
  if first.issue;

data iss_votes2a; set iss_votes2; 
  if dist ne .;

data iss_votes2; set iss_votes2; 
  if dist = .; 
  drop permno dist;
run;

proc sql;
  create table iss_votes2 as
  select a.*, b.permno, min(intck('month', b.namedt, a.meetingdate), intck('month', a.meetingdate, b.nameenddt)) as dist
  from iss_votes2 as a left join cusips as b
  on substr(a.cusip, 1, 6) = substr(b.ncusip, 1, 6) and a.ticker = b.ticker and b.namedt <= a.meetingdate <= nameenddt;
quit;

data iss_votes2b; set iss_votes2; 
  if dist ne .;

data iss_votes2c; set iss_votes2; 
  if dist = .; 
  drop permno dist;
run;

proc sql;
  create table iss_votes2x as
  select a.*, b.permno, min(abs(intck('month', b.namedt, a.meetingdate)), abs(intck('month', a.meetingdate, b.nameenddt))) as dist
  from iss_votes2c as a left join cusips as b
  on substr(a.cusip, 1, 8) = substr(b.ncusip, 1, 8);
quit;

proc sort; by cusip meetingdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid institutionid fundid mgmtFor issForMgmt fundvote issue dist;

data iss_votes2c; set iss_votes2x; by cusip meetingdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid institutionid fundid mgmtFor issForMgmt fundvote issue dist;
  if first.issue;
  if permno ne .; 
run;

data iss_votes3 (compress=yes); set iss_votes2a (in=a) iss_votes2b (in=b) iss_votes2c (in=c);
  if a then s = 1; 
  if b then s = 2; 
  if c then s = 3; 
run;

**** Link to holdings data;
proc sort data = iss_votes3 out = fund_mend0 (keep=institutionID fundid meetingdate) nodupkey; by institutionID fundid meetingdate;

data fund_mend; set fund_mend0;
  mend = intnx('month', meetingdate, 0, 'end');
  format mend date9.;

proc sql;
  create table fund_mend as
  select a.*, b.institutionName, b.fundname, b.crsp_fundno, b.wficn, b.crsp_cl_grp
  from fund_mend as a, drop.iss_crspmf as b
  where a.institutionID = b.institutionID and a.fundid = b.fundid; 
quit;

proc sort nodupkey data = fund_mend out = temp.fund_mend; by institutionID fundid mend wficn; 
run;


proc sort data = temp.s12_holdings (keep=wficn rdate fdate) nodupkey out = s12_dates; by wficn rdate fdate;
run; * temp.s12_holdings is from 02Holdings.sas;

proc sql;
  create table wficn_mend as
  select a.*, b.rdate, b.fdate
  from temp.fund_mend as a left join s12_dates as b
  on a.wficn = b.wficn and 1 <= intck('month', b.rdate, a.mend) <= 6;
quit;

proc sort data = wficn_mend; by institutionID fundid mend wficn descending rdate fdate; 

data wficn_mend; set wficn_mend; by institutionID fundid mend wficn descending rdate; 
  if first.wficn;
run;

proc sql;
  create table iss_votes3a as
  select a.*, b.crsp_cl_grp, b.wficn, b.rdate, b.fdate
  from iss_votes3 as a left join wficn_mend as b
  on a.institutionID = b.institutionID and a.fundid = b.fundid and year(a.meetingdate) = year(b.mend) and month(a.meetingdate) = month(b.mend);
quit;

proc sort data = iss_votes3a; by wficn crsp_cl_grp permno meetingdate rdate fdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid mgmtFor issFor issForMgmt issue; 

proc means data = iss_votes3a noprint; by wficn crsp_cl_grp permno meetingdate rdate fdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid mgmtFor issFor issForMgmt issue; 
  var voteWithMgmt winVote iss_conform fundFor fundAgainst;
  output out = iss_votes3a (drop=_type_ _freq_) mean = voteWithMgmt winVote iss_conform fundFor fundAgainst;
run;

***** LINK TO Thomson Reuters mutual fund holdings;
proc sql;
  create table iss_votes3s as
  select a.*, b.market_value, b.shares_adj as nbr_shares, b.wt_adj as wt, b.pwt, b.cfacshr_rdate
  from iss_votes3a as a left join temp.s12_holdings as b
  on a.wficn = b.wficn and a.rdate = b.rdate and a.fdate = b.fdate and a.permno = b.permno;
quit; * temp.s12_holdings is from 02Holdings.sas;
run;



***** LINK TO CRSPMF holdings;
data iss_votes3x; set iss_votes3s;
  if wt = .; 

proc sort data = iss_votes3x out = no_s12 (keep=wficn crsp_cl_grp permno meetingdate) nodupkey; by crsp_cl_grp meetingdate permno;

proc means noprint; by crsp_cl_grp meetingdate;
  var permno;
  output out = num_stox (drop=_type_ _freq_) n = no_stocks;

proc sort data = no_s12 (keep=wficn crsp_cl_grp meetingdate) nodupkey; by crsp_cl_grp meetingdate;

data no_s12; merge no_s12 num_stox; by crsp_cl_grp meetingdate;

proc sort data = temp.holdings out = fund_dates (keep=crsp_cl_grp report_dt) nodupkey; by crsp_cl_grp report_dt;

proc sql;
  create table no_s12 as
  select a.*, b.report_dt as rdate
  from no_s12 as a left join fund_dates as b
  on a.crsp_cl_grp = b.crsp_cl_grp and 1 <= intck('month', b.report_dt, a.meetingdate) <= 6;
quit;

proc sort data = no_s12; by crsp_cl_grp meetingdate rdate;

data no_s12; set no_s12; by crsp_cl_grp meetingdate rdate;
  if last.meetingdate and rdate ne . and crsp_cl_grp ne .;
run;

proc sql;
  create table iss_votes3x as
  select a.*, b.rdate
  from iss_votes3x (drop=rdate fdate market_value nbr_shares wt cfacshr_rdate pwt) as a left join no_s12 as b
  on a.crsp_cl_grp = b.crsp_cl_grp and a.meetingdate = b.meetingdate;
quit;
 
proc sql;
  create table iss_votes3x as
  select a.*, b.market_value, b.nbr_shares, b.wt, b.pwt, b.cfacshr_rdate
  from iss_votes3x as a, temp.holdings as b
  where a.crsp_cl_grp = b.crsp_cl_grp and a.rdate = b.report_dt and a.permno = b.permno;
quit;run; * temp.holdings is from 02Holdings.sas;
    
proc sort data = iss_votes3x; by wficn permno meetingdate rdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue voteWithMgmt winVote iss_conform; 
  where permno ne . and crsp_cl_grp ne . and wt ne .;
run;
 
proc sort data = iss_votes3s; by wficn permno meetingdate rdate mgmt_sponsored Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue voteWithMgmt winVote iss_conform; 
  where permno ne . and wficn ne . and wt ne .;
run;

data iss_votes3b; set iss_votes3s (in=a) iss_votes3x (in=b);
  if a then s12 = 1; if b then s12 = 0;
run;

**************************************************************************************************************;
**************************************************************************************************************;

proc sort data = drop.Proposal_list out = all_agenda nodupkey; by ISSAgendaItemID; 
run;

proc sql;
  create table results as
  select a.*, b.issue
  from iss.Vote_Results2003_2018 as a, all_agenda as b
  where a.ISSAgendaItemID = b.ISSAgendaItemID; 
quit;

data results; set results;
  if base in ('F+A', 'F A') then margin = votedfor/(votedfor+votedagainst)-voterequirement;
  if base in ('F+A+AB', 'F A AB') then margin = votedfor/(votedfor+votedagainst+votedabstain)-voterequirement;
  if base = 'Outstanding' then margin = votedfor/outstandingshare-voterequirement;
  if voteresult = 'Pass' then Pass = 1; else if voteresult = 'Fail' then Pass = 0;

proc sql;
  create table iss_votes3b as
  select a.*, b.MeetingType, b.base, b.voterequirement, b.margin, b.agendageneraldesc, b.mgmt_win
  from iss_votes3b as a left join results as b
  on a.itemOnAgendaId = b.itemOnAgendaId; 
quit;

proc sort data=crsp.stocknames(keep=permco permno ncusip cusip ticker comnam namedt nameenddt st_date end_date shrcd exchcd where=(not missing(ncusip))) 
  out=permno nodupkey; by permno namedt nameenddt;
run;

proc sql;
  create table iss_votes4 as
  select a.*, b.shrcd, b.exchcd
  from iss_votes3b as a left join permno as b
  on a.permno = b.permno and b.namedt <= a.meetingdate <= b.nameenddt;
quit;

proc sort data = iss_votes4 nodupkey; by _ALL_; run;

data temp.iss_votes_ret; set iss_votes4; 
  rqdate = intnx('quarter', meetingdate, -1, 'end');
  qtr = year(rqdate)*10+qtr(rqdate);
  mend = intnx('month', meetingdate, 0, 'end');
  if WinVote ne .;
  if nanalyst = . then nanalyst = 0;
  abs_margin = abs(margin);
  inv_margin = 1/abs_margin;
  year = year(meetingdate);
  voteAgainstMgmt = 1-voteWithMgmt;
  if iss_conform = 1 and voteAgainstMgmt = 1 then voteAgainstMgmt_ISS_conform = 1; else voteAgainstMgmt_ISS_conform = 0;
  if iss_conform = 0 and voteAgainstMgmt = 1 then voteAgainstMgmt_non_conform = 1; else voteAgainstMgmt_non_conform = 0;
run; 
